package com.thinkgem.jeesite.modules.guobo.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;

import com.thinkgem.jeesite.modules.guobo.entity.NCAsset;
import com.thinkgem.jeesite.modules.sys.utils.JdbcUtil;

/**
 * jdbc方式NC资产的DAO
 * @author wudavid
 *
 */
@Component("NCAssetDao")
public class NCAssetDao {
	
	private Logger logger = LoggerFactory.getLogger(getClass());

	/**
	 * 查询所有的NC固定资产
	 * 
	 * @return
	 */
	public List<NCAsset> findAllNCAssets() {
		logger.info("进入NC接口>>>>>>>>>>");
		StringBuilder sql = new StringBuilder();
		sql.append("select code, name, spec, model, type, typename, status,  managerid,  manager,  mandeptid,  mandeptname,sumnum ");
		sql.append("  from (select t.equip_code as code,                           ");
		sql.append("               t.equip_name as name,                           ");
		sql.append("               t.spec as spec,                                 ");
		sql.append("               t.model as model,                               ");
		sql.append("               t.pk_category as type,                          ");
		sql.append("               cate.category_name as typename,                 ");
		sql.append("               t.dr as status,                                 ");
		sql.append("               t.pk_user as managerid,                         ");
		sql.append("               ps.name as manager,                             ");
		sql.append("               t.pk_mandept as mandeptid,                      ");
		sql.append("               dept.name as mandeptname,                       ");
		sql.append("               case                                            ");
		sql.append("                 when t.def2 = '~' then                        ");
		sql.append("                  '0'                                          ");
		sql.append("                 else                                          ");
		sql.append("                  t.def2                                       ");
		sql.append("               end as sumnum                                   ");
		sql.append("          from pam_equip t                                     ");
		sql.append("          left join pam_category cate                          ");
		sql.append("            on cate.pk_category = t.pk_category                ");
		sql.append("          left join bd_psndoc ps                               ");
		sql.append("            on ps.pk_psndoc = t.pk_user                        ");
		sql.append("          left join org_dept dept                              ");
		sql.append("            on dept.pk_dept = t.pk_mandept                     ");
		sql.append("         where t.dr = 0 )                                      ");
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		List<NCAsset> ncAssets = new ArrayList<NCAsset>();
		try {
			con = JdbcUtil.getConnection();
			stmt = con.prepareStatement(sql.toString());
			logger.debug("NC 接口执行 SQL>>>>>>>"+sql.toString());
			logger.info("NC 接口执行 SQL>>>>>>>"+sql.toString());
			rs = stmt.executeQuery();
			while (rs.next()) {
				NCAsset nc = new NCAsset();
				nc.setCode(rs.getString("code"));
				nc.setName(rs.getString("name"));
				nc.setSpec(rs.getString("spec"));
				nc.setModel(rs.getString("model"));
				nc.setType(rs.getString("type"));
				nc.setTypeName(rs.getString("typename"));				
				nc.setStatus(rs.getString("status"));
				nc.setManagerId(rs.getString("managerid"));
				nc.setManagerName(rs.getString("manager"));
				nc.setMandeptId(rs.getString("mandeptid"));
				nc.setMandeptName(rs.getString("mandeptname"));
				nc.setSumnum(Long.parseLong(rs.getString("sumnum")));
				ncAssets.add(nc);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.close(rs, stmt, con);
		}
		logger.debug("返回记录数>>>>>>>>>>"+ncAssets.size());
		logger.info("返回记录数>>>>>>>>>>"+ncAssets.size());
		return ncAssets;
	}
	
	/**
	 * 分页查询NC固定资产
	 * @param page
	 * @param pageSize
	 * @param param
	 * @return
	 */
	public List<NCAsset> findPageNCAssets(int page,int pageSize,Map<String,Object> param) {
		logger.info("进入NC接口>>>>>>>>>>");
		int begin = (page-1)*pageSize+1;
	    int end = begin+pageSize-1;
		StringBuilder sql = new StringBuilder();
		List<Object> conditon = new ArrayList<Object>();
		conditon.add(page);
		conditon.add(pageSize);
		sql.append("select code, name, spec, model, type, typename, status,  managerid,  manager,mobile,  mandeptid,  mandeptname,sumnum ");
		sql.append(" from (select code, name, spec, model, type, typename, status,  managerid,  manager,mobile,  mandeptid,  mandeptname,sumnum,rownum as rn ");
		sql.append("  from (select t.equip_code as code,                           ");
		sql.append("               t.equip_name as name,                           ");
		sql.append("               t.spec as spec,                                 ");
		sql.append("               t.model as model,                               ");
		sql.append("               t.pk_category as type,                          ");
		sql.append("               cate.category_name as typename,                 ");
		sql.append("               t.dr as status,                                 ");
		sql.append("               t.pk_user as managerid,                         ");
		sql.append("               ps.name as manager,                             ");
		sql.append("               ps.mobile as mobile,                             ");
		sql.append("               t.pk_mandept as mandeptid,                      ");
		sql.append("               dept.name as mandeptname,                       ");
		sql.append("               case                                            ");
		sql.append("                 when t.def2 = '~' then                        ");
		sql.append("                  '0'                                          ");
		sql.append("                 else                                          ");
		sql.append("                  t.def2                                       ");
		sql.append("               end as sumnum                                   ");
		sql.append("          from pam_equip t                                     ");
		sql.append("          left join pam_category cate                          ");
		sql.append("            on cate.pk_category = t.pk_category                ");
		sql.append("          left join bd_psndoc ps                               ");
		sql.append("            on ps.pk_psndoc = t.pk_user                        ");
		sql.append("          left join org_dept dept                              ");
		sql.append("            on dept.pk_dept = t.pk_mandept                     ");
		sql.append("         where t.dr = 0)                                       ");
		sql.append(" where rownum<=?                              ");
		List<Object> tempParam = new ArrayList<Object>();
		String name = param.get("name")+"";
		logger.info("参数 name>>>>>>>"+name);
		if(name!=null && !"null".equals(name) && !"".equals(name)){
			sql.append(" and name   like ?                     ");
			tempParam.add("%"+name.trim()+"%");  
		}
		String model = param.get("model")+"";
		if(model!=null && !"null".equals(model) && !"".equals(model)){
			sql.append(" and model   like ?                     ");
			tempParam.add("%"+model.trim()+"%");  
		}
		String code = param.get("code")+"";
		if(code!=null && !"null".equals(code) && !"".equals(code)){
			sql.append(" and code=?                     ");
			tempParam.add(code.trim());  
		}
		sql.append(" )  where rn >= ?");
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		List<NCAsset> ncAssets = new ArrayList<NCAsset>();
		try {
			con = JdbcUtil.getConnection();
			stmt = con.prepareStatement(sql.toString());
			stmt.setInt(1, end);
			int i=0;
			if(tempParam.size()>0){
				for (Object p : tempParam) {
					stmt.setString(i+2, tempParam.get(i)+"");
					i=i+1;
				}				
			}
			stmt.setInt(i+2, begin);
			rs = stmt.executeQuery();
			while (rs.next()) {
				NCAsset nc = new NCAsset();
				nc.setCode(rs.getString("code"));
				nc.setName(rs.getString("name"));
				nc.setSpec(rs.getString("spec"));
				nc.setModel(rs.getString("model"));
				nc.setType(rs.getString("type"));
				nc.setTypeName(rs.getString("typename"));				
				nc.setStatus(rs.getString("status"));
				nc.setManagerId(rs.getString("managerid"));
				nc.setManagerName(rs.getString("manager"));
				nc.setMobile(rs.getString("mobile"));
				nc.setMandeptId(rs.getString("mandeptid"));
				nc.setMandeptName(rs.getString("mandeptname"));
				nc.setSumnum(Long.parseLong(rs.getString("sumnum")));
				ncAssets.add(nc);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.close(rs, stmt, con);
		}
		logger.debug("返回记录数>>>>>>>>>>"+ncAssets.size());
		logger.info("返回记录数>>>>>>>>>>"+ncAssets.size());
		return ncAssets;
	}
	
	/**
	 * 分页查询NC非固定资产
	 * @param page
	 * @param pageSize
	 * @param param
	 * @return
	 */
	public List<NCAsset> findPageNCMaterialAssets(int page,int pageSize,Map<String,Object> param) {
		int begin = (page-1)*pageSize+1;
	    int end = begin+pageSize-1;
		StringBuilder sql = new StringBuilder();
		List<Object> conditon = new ArrayList<Object>();
		conditon.add(page);
		conditon.add(pageSize);
		sql.append("select code, name, spec, type, pk_brand, brandname");
		sql.append(" from (select code, name, spec, type, pk_brand, brandname,rownum as rn ");
		sql.append("  from (select t.code,                               ");
		sql.append("               t.name,                               ");
		sql.append("               t.materialspec as spec,               ");
		sql.append("               t.materialtype as type,               ");
		sql.append("               case                                  ");
		sql.append("                 when t.pk_brand = '~' then          ");
		sql.append("                  ''                                 ");
		sql.append("                 else                                ");
		sql.append("                  t.pk_brand                         ");
		sql.append("               end as pk_brand,                      ");
		sql.append("               brand.name as brandname               ");
		sql.append("          from bd_material t                         ");
		sql.append("          left join bd_branddoc brand                ");
		sql.append("            on brand.pk_brand = t.pk_brand           ");
		sql.append("         where t.dr = '0')                           ");
		sql.append(" where rownum<=?                    ");
		List<Object> tempParam = new ArrayList<Object>();
		String name = param.get("name")+"";
		if(name!=null && !"null".equals(name) && !"".equals(name)){
			sql.append(" and name   like ?                     ");
			tempParam.add("%"+name.trim()+"%");  
		}		
		String code = param.get("code")+"";
		if(code!=null && !"null".equals(code) && !"".equals(code)){
			sql.append(" and code=?                     ");
			tempParam.add(code.trim());  
		}
		sql.append(" ) where rn>=?                    ");
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		List<NCAsset> ncAssets = new ArrayList<NCAsset>();
		try {
			con = JdbcUtil.getConnection();
			stmt = con.prepareStatement(sql.toString());
			stmt.setInt(1, end);
			int i=0;
			if(tempParam.size()>0){
				for (Object p : tempParam) {
					stmt.setString(i+2, tempParam.get(i)+"");
					i=i+1;
				}				
			}
			stmt.setInt(i+2, begin);
			rs = stmt.executeQuery();
			while (rs.next()) {
				NCAsset nc = new NCAsset();
				nc.setCode(rs.getString("code"));
				nc.setName(rs.getString("name"));
				nc.setSpec(rs.getString("spec"));
				nc.setType(rs.getString("type"));
				nc.setPkBrand(rs.getString("pk_brand"));				
				nc.setBrand(rs.getString("brandname"));
				ncAssets.add(nc);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.close(rs, stmt, con);
		}
		return ncAssets;
	}
}
